<?php
// Check to ensure this file is included in Joomla!
defined('_JEXEC') or die();
jimport('joomla.application.component.model');  

class WrdModelReports extends WrdModel 
{
    var $_data;
	var $_id;
	var $_total = null;
	var $_pagination = null;
	var $_limit = null;
	var $_limitstart = null;
	
	public function __construct(){
		parent::__construct();
		$mainframe = &JFactory::getApplication();
		$context = JRequest::getCmd('option');
		$view = JRequest::getCmd('view');
		
        /******** Check limit, offset ********/
        if($_POST['submitViewExcel'] == 'submitViewExcel')
        {
            $mainframe->setUserState( $context.$view.'limit', 20);
            $mainframe->setUserState( $context.$view.'limitstart', 0 );    
        }
        $this->_limit = $mainframe->getUserStateFromRequest( $context.$view.'limit', 'limit', $mainframe->getCfg('list_limit'), 20);
        $this->_limitstart = $mainframe->getUserStateFromRequest( $context.$view.'limitstart', 'limitstart', 0 );    
        		
        //var_dump($this->_limit, $this->_limitstart);
		$this->setState('limit', $this->_limit);
		$this->setState('limitstart', $this->_limitstart);
	}
    
    /*
     * @param: total = 0 -> count total item, type = 1 -> get items
     *   
     */
    public function getTransaction($total = 1)
    {
        $mainframe = &JFactory::getApplication();
        $context = JRequest::getCmd('option');
        $view = JRequest::getCmd('view');
        
        if($total == 0)
        {
            $query = '
                   SELECT
                      count(*)
                    FROM #__wrd_transaction `t`                     
                    WHERE `t`.convert = 1
            ' ;
        }
        else
        {
            
            $query = '
               SELECT
                  `t`.id             AS `id`,
                  `t`.pax            AS `pax`,
                  `t`.total          AS `total`,
                  `t`.beverage       AS `beverage`,
                  `t`.food           AS `food`,
                  `t`.vip_number     AS `vip_number`,
                  `t`.other          AS `other`,
                  `t`.vip_number     AS `vip_number`,
                  `t`.transaction_id AS `check`,
                  `t`.liquor         AS `liquor`,
                  `t`.date_receipt   AS `date`,
                  `p`.f_name         AS `f_name`,
                  `p`.l_name         AS `l_name`,
                  `o`.NAME           AS `outlet`
                FROM #__wrd_transaction `t`
                  LEFT JOIN #__wrd_outlets `o`
                    ON `o`.code = `t`.outlet
                  LEFT JOIN #__wrd_profile `p`
                    ON `p`.member_id = `t`.vip_number
                WHERE `t`.convert = 1 
            ' ;    
        }
        
        if($total == 1){
            $filter_order     = $mainframe->getUserStateFromRequest( $context.$view.'filter_order_author','filter_order','member_id' );
            $filter_order_Dir = $mainframe->getUserStateFromRequest( $context.$view.'filter_order_Dir',  'filter_order_Dir', 'DESC' );
            $query .= ' ORDER BY '.$filter_order.' '.$filter_order_Dir;
            $this->_db->setQuery($query, $this->_limitstart, $this->_limit );
            $data = $this->_db->loadObjectList();
            if(!empty($data))
            {
                $transaction_id = '';
                $size = count($data);
                for($i=0; $i<$size; $i++)
                {
                    $transaction_id .= $data[$i]->check.',';
                }
                $transaction_id = trim($transaction_id, ',');
                $query = 'SELECT `point`, `transaction` FROM #__wrd_point WHERE `transaction` IN ('.$transaction_id.')';
                $this->_db->setQuery($query) ;
                $point = $this->_db->loadObjectList();
                if(!empty($point))
                {
                    foreach($point as $item)
                    {
                        for($i=0; $i<$size; $i++)
                        {
                            if($data[$i]->check == $item->transaction)
                            {
                                $data[$i]->liquor = $item->point;
                                break;
                            }
                        }
                    }
                }
            }
            return $data;
        }else{
            $this->_db->setQuery($query) ;
            return $this->_db->loadResult();
        }
    }
    
    /*
     * @param: total = 0 -> count total item, type = 1 -> get items
     *   
     */
    public function getTopMemberPending($total = 1, $date_from, $date_to, $conditions = array())
    {
        $mainframe = &JFactory::getApplication();
        $context = JRequest::getCmd('option');
        $view = JRequest::getCmd('view');
        
        if($total == 0)
        {
            $query = "
                SELECT
                  COUNT(*)
                FROM #__wrd_outlets `o`,
                  #__wrd_transaction `t`
                WHERE `t`.CONVERT = 1
                    AND `t`.outlet = `o`.CODE
                    AND `o`.cid = '01'
            ";
        }
        else
        {            
            $query = "
                SELECT
                  `t`.id             AS `id`,
                  `t`.vip_number     AS `vip_number`,
                  SUM(`t`.total)     AS `total`,
                  SUM(`t`.beverage)  AS `beverage`,
                  SUM(`t`.food)      AS `food`,
                  SUM(`t`.other)     AS `other`,
                  COUNT(`t`.transaction_id) AS `total_visit`,
                  `t`.transaction_id AS `check`,
                  `t`.liquor         AS `liquor`,
                  `o`.NAME           AS `outlet`,
                  `p`.gender         AS `gender`,
                  `p`.l_name         AS `l_name`,
                  `p`.f_name         AS `f_name`,
                  `p`.company_name   AS `company`,
                  `p`.p_email        AS `email`,
                  `p`.phone          AS `phone`,
                  `p`.user_id        AS `user_id`,
                  `p`.id             AS `profile_id`,
                  `j`.NAME           AS `job`
                FROM #__wrd_outlets `o`, #__wrd_transaction `t`
                  LEFT JOIN #__wrd_profile `p`
                    ON `p`.member_id = `t`.vip_number
                  LEFT JOIN #__wrd_job `j`
                    ON `j`.id = `p`.job
                WHERE `t`.CONVERT = 1                    
                    AND `o`.cid = '01' 
                    AND `t`.outlet = `o`.CODE
            ";
        }
        if(!empty($conditions) && !empty($conditions['outlet']) && $conditions['outlet'][0] != '')
        {
            $conditions['outlet'] = implode(',', $conditions['outlet']);
            $query .= ' AND `o`.CODE IN ('.$conditions['outlet'].') ';
        }
       
        if(!empty($date_from)){
            $query .= ' AND `t`.date_receipt >= "'.$date_from.'"'; 
        }
        if(!empty($date_to)){
            $query .= ' AND `t`.date_receipt <= "'.$date_to.'" ' ; 
        }
        
        
        if($total == 1){
            $query .= '
                GROUP BY `t`.vip_number
                ORDER BY total DESC
            ';
            //echo $query;
            $this->_db->setQuery($query, $this->_limitstart, $this->_limit );
            $data = $this->_db->loadObjectList();
            
            if(!empty($data))
            {
                /************* Get transaction id *************/
                $vip_number = '';
                $size = count($data);
                for($i=0; $i<$size; $i++)
                {
                    $vip_number .= $data[$i]->vip_number.',';
                }
                $vip_number = trim($vip_number, ',');
                
                $query = 'SELECT
                            `t`.transaction_id AS `transaction_id`,
                            `t`.vip_number     AS `vip_number`,
                            `t`.pax            AS `pax`
                          FROM #__wrd_transaction `t`
                          WHERE `t`.vip_number IN ('.$vip_number.')';
                $this->_db->setQuery($query) ;
                $transactions = $this->_db->loadObjectList();
                
                $transaction_id = '';
                $countTransaction = count($transactions);
                if(!empty($transactions))
                {
                    for($i=0; $i<$countTransaction; $i++)
                    {
                        $transaction_id .= $transactions[$i]->transaction_id.',';
                        $transactions[$i]->pax = 0;
                    }
                }
                $transaction_id = trim($transaction_id, ',');
                
                /************* Get point *************/
                $query = 'SELECT `point` as `point`, `transaction` as `transaction` FROM #__wrd_point WHERE `transaction` IN ('.$transaction_id.')';
                $this->_db->setQuery($query) ;
                $point = $this->_db->loadObjectList();
                $arrPoint = array();
                if(!empty($point))
                {
                    for($i=0; $i<$countTransaction; $i++)
                    {
                        foreach($point as $item)
                        {
                            if($transactions[$i]->transaction_id == $item->transaction)
                            {                                
                                $transactions[$i]->pax = $item->point;
                                
                                break;
                            }                            
                        }
                    }
                    //var_dump($transactions); die;
                    /******* Add point for vip number *******/
                    for($i=0; $i<$countTransaction; $i++)
                    {
                        $arrPoint[$transactions[$i]->vip_number] = intval($arrPoint[$transactions[$i]->vip_number]) + $transactions[$i]->pax;
                    }
                    
                    /******* Add point for tranaction *******/
                    for($i=0; $i<$size; $i++)
                    {
                        foreach($arrPoint as $key => $value)
                        {
                            if($data[$i]->vip_number == $key)
                            {
                                $data[$i]->liquor = $value;
                                break;   
                            }
                        }                        
                    }
                    
                }
                
            }
            return $data;
        }else{
            $query .= ' GROUP BY `t`.vip_number ';
            //echo $query; die;
            $this->_db->setQuery($query) ;
            return count($this->_db->loadObjectList());
        }
    }
    
    /*
     * @param: total = 0 -> count total item, type = 1 -> get items
     *   
     */
    public function revenuePendingReport($total = 1, $date_from, $date_to, $conditions = array())
    {
        $mainframe = &JFactory::getApplication();
        $context = JRequest::getCmd('option');
        $view = JRequest::getCmd('view');
        
        if($total == 0)
        {
            $query = "
                SELECT
                    count(*)
                FROM #__wrd_outlets `o`,
                    #__wrd_transaction `t`
                WHERE `t`.CONVERT = 1
                      AND `o`.cid = '01'
                      AND `t`.outlet = `o`.CODE
                
            ";
        }
        else
        {   
            /********************* BUILD QUERY *********************/
            $query = "
                SELECT
                    `o`.NAME AS `outlet`,
                    SUM(`t`.pax) AS `cover`,
                    SUM(`t`.food) AS `food`,
                    SUM(`t`.beverage) AS `beverage`,
                    SUM(`t`.other) AS `other`,
                    SUM(`t`.vat) AS `vat`,
                    SUM(`t`.sc) AS `sc`,
                    SUM(`t`.total) AS `total`
                FROM #__wrd_outlets `o`,
                    #__wrd_transaction `t`
                WHERE `t`.CONVERT = 1
                      AND `o`.cid = '01'
                      AND `t`.outlet = `o`.CODE
            ";
        }
        
       //echo $query; die;
        if(!empty($date_from)){
            $query .= ' AND `t`.date_receipt >= "'.$date_from.'"'; 
        }
        if(!empty($date_to)){
            $query .= ' AND `t`.date_receipt <= "'.$date_to.'" ' ; 
        }        
        //echo $query;die;
        if($total == 1){           
            $query .= '
                GROUP BY `o`.NAME
                ORDER BY total DESC
            ';
            //echo $query.'__'.$this->_limitstart.'__'. $this->_limit;exit;
            $this->_db->setQuery($query, $this->_limitstart, $this->_limit );
            $data = $this->_db->loadObjectList();
                        
            return $data;
        }else{
            $query .= ' GROUP BY `o`.NAME ';
            //echo $query; die;
            $this->_db->setQuery($query) ;
            return count($this->_db->loadObjectList());
        }
    }
    
    /*
     * @param: total = 0 -> count total item, type = 1 -> get items
     * Report VIP Membership Club  Detail
     */
    public function membershipclubdetail($month, $year, $day, $conditions)
    {
        $result = array();
        
        /******** Set up query ********/
        $query = reportProvider::membershipClubDetailQuery($month, $year, $day, $conditions['extra_status']);
        /******** Query Data ********/                
        $this->_db->setQuery($query['total']);
        $result['total'] = $this->_db->loadResult();
        
        $this->_db->setQuery($query['items'], $this->_limitstart, $this->_limit );
        $result['items'] = $this->_db->loadObjectList();
            
        /******** Return Data ********/
        return $result;
    }
    
    /*
     * Update pickup date
     */
    public function updatePickUpDate($conditions)
    {
        $query = "
            UPDATE #__wrd_profile
            SET pick_up_date = '".$conditions['d']."'
            WHERE id = '".$conditions['id']."'
        ";
        $this->_db->setQuery($query);
        return $this->_db->query(); 
        
    }
    
    /*
     * Update redemption date
     */
    public function updateRedemptionDate($conditions)
    {
        if($conditions['type'] == 1) {
            $query = "
                UPDATE #__wrd_redemption
                SET pick_up_date = '".$conditions['d']."'
                WHERE id = '".$conditions['id']."'
            ";    
        } else {
            $query = "
                UPDATE #__wrd_redemption
                SET status_of_redemption_date = '".$conditions['d']."'
                WHERE id = '".$conditions['id']."'
            ";
        }
        
        $this->_db->setQuery($query);
        return $this->_db->query(); 
        
    }
    
    /*
     * @param: total = 0 -> count total item, type = 1 -> get items
     * Report VIP Membership Club  
     */
    public function membershipClub($month, $year)
    {
        
		/******** Query Data ********/
        $query = reportProvider::membershipClubQuery($month, $year);
        $result = array();
        
        $this->_db->setQuery($query['new_join_member']);
        $result['new_join_member'] = $this->_db->loadResult();
        
        $this->_db->setQuery($query['member_total']);
        $result['member_total'] = $this->_db->loadResult();
        
        $this->_db->setQuery($query['active_member']);
        $result['active_member'] = $this->_db->loadObjectList();
        $result['active_member'] = count($result['active_member']);
        
        $this->_db->setQuery($query['pick_up_member']);
        $result['pick_up_member'] = $this->_db->loadResult();
       // echo '<pre>';print_r($result);
        /******** Return Data ********/
        return $result;
    }
    
    public function redemptionsummary($month, $year)
    {
        /******** Query Data ********/
        $query = reportProvider::membershipClubQuery($month, $year);
        $result = array();
        
        $this->_db->setQuery($query['new_join_member']);
        $result['new_join_member'] = $this->_db->loadResult();
        
        $this->_db->setQuery($query['member_total']);
        $result['member_total'] = $this->_db->loadResult();
        
        $this->_db->setQuery($query['active_member']);
        $result['active_member'] = $this->_db->loadObjectList();
        $result['active_member'] = count($result['active_member']);
        
        $this->_db->setQuery($query['pick_up_member']);
        $result['pick_up_member'] = $this->_db->loadResult();
        
        /******** Return Data ********/
        return $result;
    }
    /*
     * Member birthday
     */
    public function memberbirthday($conditions = array())
    {
        $conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
      //  echo '<pre>';print_r($conditions);        
        /********************* BUILD QUERY *********************/
        $data = reportProvider::memberbirthday($conditions, $this->_db);
        
        return $data;
    }
	/*
     * Redemption report
     */
    public function redemptionreport($conditions = array())
    {
        $conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
                
        /********************* BUILD QUERY *********************/
        $data = reportProvider::redemptionreport($conditions, $this->_db);
        
        return $data;
    }
    /*
     * Redemption report new
     */
    public function redemptionreportnew($conditions = array())
    {
        $conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
                
        /********************* BUILD QUERY *********************/
        $data = reportProvider::redemptionreportnew($conditions, $this->_db);
        
        return $data;
    }
    
	public function transactionreport($conditions = array())
    {
        
		$conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
        //echo "<pre>";print_r($conditions);exit;
        $data = reportProvider::transactionreport($conditions, $this->_db);
        return $data;
    }
	public function transactionreportweb($conditions = array())
    {
        
		$conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
        //echo "<pre>";print_r($conditions);exit;
        $data = reportProvider::transactionreportweb($conditions, $this->_db);
        return $data;
    }
	/*
     * Redemption report new
     */
    public function redemptionreportproduct($conditions = array())
    {
        $conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
                
        /********************* BUILD QUERY *********************/
        $data = reportProvider::redemptionreportproduct($conditions, $this->_db);
        
        return $data;
    }
    
	/*
     * Redemption report new
     */
    public function redemptionreportvoucher($conditions = array())
    {
        $conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
        /********************* BUILD QUERY *********************/
        $data = reportProvider::redemptionreportvoucher($conditions, $this->_db);
        
		return $data;
    }
    
	public function memberstatisticjob($conditions = array())
    {
        $conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
        /********************* BUILD QUERY *********************/
        $data = reportProvider::memberstatisticjob($conditions, $this->_db);
        
		return $data;
    }
    
	public function memberreport($conditions = array())
    {
        $conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
        /********************* BUILD QUERY *********************/
        $data = reportProvider::memberreport($conditions, $this->_db);
		return $data;
    }
	public function outletreport($conditions = array())
    {
        $conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
        /********************* BUILD QUERY *********************/
        $data = reportProvider::outletreport($conditions, $this->_db);
		return $data;
    }
	/*
     * Redemption report new
     */
    public function redempreport($conditions = array())
    {
       
		$conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
        /********************* BUILD QUERY *********************/
        $data = reportProvider::redempreport($conditions, $this->_db);
	   return $data;
    }
    
	public function redempreportdetail($conditions = array())
    {
       
		$conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
        /********************* BUILD QUERY *********************/
        $data = reportProvider::redempreportdetail($conditions, $this->_db);
	   return $data;
    }
	
	public function approveVoucher($conditions = array())
    {
        $limit = $conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
		$query = array();
       
        $query['items'] = "
            SELECT v.id,p.name AS productName,v.code_redemption,v.code_voucher,v.expired,v.date_voucher,
			v.outlet_name_use_voucher,v.outlet_voucher,v.member_id
            FROM #__wrd_redem_report_voucher `v`
			INNER JOIN #__wrd_products as p ON v.product_id = p.id
			WHERE 1
			
        ";
        $query['total'] = "
            SELECT count(*)
            FROM #__wrd_redem_report_voucher `v`
			INNER JOIN #__wrd_products as p ON v.product_id = p.id
			WHERE 1
			
        ";
		//GROUP BY v.code_voucher
	   $extraQuery = '';
	   if($conditions['sredem'] != '')
        {
            $extraQuery .= " AND `v`.code_redemption = '".$conditions['sredem']."'"; 
        }
		if($conditions['member_id'] != '')
        {
            $extraQuery .= " AND `v`.member_id = '".$conditions['member_id']."'"; 
        }
		 if($conditions['code_voucher'] != '')
        {
            $extraQuery .= " AND `v`.code_voucher = '".$conditions['code_voucher']."'"; 
        }
	   if($conditions['status'] != '' && $conditions['status'] != -1)
        {
            $extraQuery .= " AND `v`.status = '".$conditions['status']."'"; 
        }		
		
       $data = array(); 
	   
		$query['total'] .= $extraQuery;      
        $query['items'] .= $extraQuery." ORDER BY id DESC";
		
		$this->_db->setQuery($query['items']);
		if($limit > 0){
            $this->_db->setQuery($query['items'], 0, $limit);
        }
     
        if($conditions['limitend'] > 0) {
            $this->_db->setQuery($query['items'], $conditions['limitstart'], $conditions['limitend']);
        }
		$data['items'] = $this->_db->loadObjectList();
       
        $this->_db->setQuery($query['total']);
        $data['total'] = $this->_db->loadResult();
		return $data;
    }
    
    public function memberServiceReport($conditions = array())
    {
        $conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
        
        $data = reportProvider::memberServiceReport($conditions, $this->_db);
        return $data;
    }
    
    public function complainReport($conditions = array())
    {
        $conditions['limitstart'] = $this->_limitstart;
        $conditions['limitend'] = $this->_limit;
        
        $data = reportProvider::complainReport($conditions, $this->_db);
        return $data;
    }
    
    public function memberStatistic($conditions = array())
    {
        $query = reportProvider::memberStatistic($conditions);
        if($conditions['statistic_by'] != ''){
            $this->_db->setQuery($query);
            $data = $this->_db->loadObjectList();    
        }
        else
        {
            $data = array();
            $this->_db->setQuery($query['gender']);
            $data[] = $this->_db->loadObjectList();
            
            $this->_db->setQuery($query['age']);
            $data[] = $this->_db->loadObjectList();
            
            $this->_db->setQuery($query['job']);
            $data[] = $this->_db->loadObjectList();
            
            $this->_db->setQuery($query['location']);
            $data[] = $this->_db->loadObjectList();
        }
        return $data;
    }
    
    public function memberStatisticFilter($conditions = array())
    {
        /********************* BUILD QUERY *********************/
        $query = reportProvider::memberStatisticFilter($conditions);
		$this->_db->setQuery($query['items'], $this->_limitstart, $this->_limit );    
        $data['items'] = $this->_db->loadObjectList();
        
        $this->_db->setQuery($query['total']) ;
        $data['total'] = count($this->_db->loadObjectList());    
        return $data;
    }
    
    /*
     * @param: total = 0 -> count total item, type = 1 -> get items
     *   
     */
    public function revenuePendingReportDetail($total = 1, $date_from, $date_to, $conditions = array())
    {
        $mainframe = &JFactory::getApplication();
        $context = JRequest::getCmd('option');
        $view = JRequest::getCmd('view');
        
        if($total == 0)
        {
            $query = "
                SELECT
                    count(*)
                FROM #__wrd_outlets `o`,
                    #__wrd_transaction `t`
                WHERE `t`.CONVERT = 1
                      AND `o`.cid = '01'
                      AND `t`.outlet = `o`.CODE";
            
            if(!empty($date_from)){
                $query .= ' AND `t`.date_receipt >= "'.$date_from.'"'; 
            }
            if(!empty($date_to)){
                $query .= ' AND `t`.date_receipt <= "'.$date_to.'" ' ; 
            }
        }
        
        
        if($total == 1){    
            $query = reportProvider::revenuePendingReportDetailQuery($date_from, $date_to, $conditions);
            
            $this->_db->setQuery($query, $this->_limitstart, $this->_limit );
            $data = $this->_db->loadObjectList();
                        
            return $data;
        }else{
            $query .= ' GROUP BY `o`.NAME ';
            
            $this->_db->setQuery($query) ;
            return count($this->_db->loadObjectList());
        }
    }
    
    public function revenuePendingReportDetailQuery($date_from, $date_to, $conditions){
        $query = "SELECT 
                `o`.NAME AS `outlet`,
                `t`.date_receipt AS `date_receipt`,
                SUM(`t`.pax) AS `cover`,
                COUNT(`t`.transaction_id) AS `total_visit`,
                SUM(`t`.food) AS `food`,
                SUM(`t`.beverage) AS `beverage`,
                SUM(`t`.other) AS `other`,
                SUM(`t`.vat) AS `vat`,
                SUM(`t`.sc) AS `sc`,
                SUM(`t`.total) AS `total`
            FROM #__wrd_outlets `o`,
                #__wrd_transaction `t`
            WHERE `t`.CONVERT = 1
                  AND `o`.cid = '01'
                  AND `t`.outlet = `o`.CODE";

        if(!empty($date_from)){
            $query .= ' AND `t`.date_receipt >= "'.$date_from.'"'; 
        }
        if(!empty($date_to)){
            $query .= ' AND `t`.date_receipt <= "'.$date_to.'" ' ; 
        }
        
        
        /******** Add Group Condition ********/
        $groupBy .= '
            GROUP BY `o`.NAME                
        ';       
        
        /******** Add Sort by Condition ********/
        if($conditions['extra_sort_by'] != '')
        {
            switch($conditions['extra_sort_by'])
            {
                case 'dateasc':
                    $orderBy .= '
                        ORDER BY `t`.date_receipt ASC
                    ';
                    break;
                
                case 'datedesc':
                    $orderBy .= '
                        ORDER BY `t`.date_receipt DESC
                    ';
                    break;
                
                case 'outlet':
                    if(!empty($conditions['outlet']) && $conditions['outlet'][0] != '')
                    {
                        $conditions['outlet'] = implode(',', $conditions['outlet']);
                        $query .= ' AND `o`.CODE IN ('.$conditions['outlet'].') ';
                    }
                    $orderBy .= '
                        ORDER BY `t`.date_receipt ASC
                    ';
                    break;
                
                case 'meal':
                    $orderBy .= '
                        ORDER BY total DESC
                    ';
                    break;
            }
        }
        
        
        /******** Build Full Query ********/
        $query .= $groupBy.' '.$orderBy;
        
        
        /******** Return Query ********/
        return $query;
    }
    
    private function revenuePendingReportExtra($fields)
    {
        $result= '';
        switch($fields)
        {
            case 'vip_number':  $result = ' `t`.vip_number     AS `vip_number` ';    break;
            case 'prefix':      $result = ' `p`.prefix         AS `prefix` ';    break;
            case 'l_name':      $result = ' `p`.l_name         AS `l_name`,  `p`.f_name         AS `f_name` ';    break;
            case 'job':         $result = ' `j`.NAME           AS `job` ';    break;
            case 'company':     $result = ' `p`.company_name   AS `company` ';    break;
            case 'email':       $result = ' `p`.p_email        AS `email` ';    break;
            case 'phone':       $result = ' `p`.phone          AS `phone` ';    break;
            case 'total_visit': $result = ' COUNT(`t`.transaction_id) AS `total_visit` ';    break;
            case 'pax':         $result = ' SUM(`t`.pax)       AS `pax` ';    break;
            case 'total':       $result = ' SUM(`t`.total)     AS `total` ';    break;
            case 'liquor':      $result = ' `t`.liquor         AS `liquor` ';    break;
            case 'food':        $result = ' SUM(`t`.food)      AS `food` ';    break;
            case 'beverage':    $result = ' SUM(`t`.beverage)  AS `beverage` ';    break;
            case 'other':       $result = ' SUM(`t`.other)     AS `other` ';    break;                
        }
        return $result;
    }
    
   	public function getListProfiles( $date_from, $date_to, $condition = '', $total = 0 ){
   		$mainframe = &JFactory::getApplication();
		$context = JRequest::getCmd('option');
		$view = JRequest::getCmd('view');
   		$search = $mainframe->getUserStateFromRequest( $context.$view.'search','search','','string');	
   		if($total == 0){
   			$query = '
				select 
					p.id, p.prefix, p.user_id, p.f_name, p.l_name, p.published, p.created_date, p.dob,
					p.member_id, p.phone, p.address, p.c_addr, p.c_info, p.step, p.website, p.skype,
					p.yahoo, p.gtalk, p.social, p.msn, p.modify_date,p.job, job.name as job_name, p.job_prefix, p.o_job, u.name as created_per,
					pu.email, p.industry, p.department, p.education, p.o_club, p.income, p.hobbies, p.c_country,
					p.c_city, p.c_distric, ctry.name as ctry_country, cty.name as cty_city, dtric.name as dis_distric,
					p.referer, p.status, p.country_code, p.p_email, p.nationality, p.name_on_card, p.company_name,
					p.modify_by, p.delivery_date, p.pick_up_date
								
					FROM #__wrd_profile as p 
					LEFT JOIN #__users as pu ON pu.id = p.user_id 
					LEFT JOIN #__users as u ON u.id = p.created_id
					LEFT JOIN #__wrd_country as ctry ON ctry.id = p.country
					LEFT JOIN #__wrd_city as cty ON cty.id = p.city
					LEFT JOIN #__wrd_job as job ON job.id = p.job
					LEFT JOIN #__wrd_distric as dtric ON dtric.id = p.distric		
			' ;
   		}else{
   			$query = '
   				select 
					count(p.id) as total
					FROM #__wrd_profile as p 
					LEFT JOIN #__users as pu ON pu.id = p.user_id 
					LEFT JOIN #__users as u ON u.id = p.created_id
					LEFT JOIN #__wrd_country as ctry ON ctry.id = p.country
					LEFT JOIN #__wrd_city as cty ON cty.id = p.city
					LEFT JOIN #__wrd_job as job ON job.id = p.job
					LEFT JOIN #__wrd_distric as dtric ON dtric.id = p.distric		
			' ;
   		}
		$query .= ' where (1) ' ;
		if(!empty($date_from)){
			$query .= ' and p.created_date >= "'.$date_from.'"'; 
		}
		if(!empty($date_to)){
			$query .= ' and p.created_date <= "'.$date_to.'" ' ; 
		}
		if(!empty($condition)){
			$query .= $condition ; 
		}
   		$search = JString::strtolower( $search );
		if ($search){
			$query .= ' and p.member_id LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ); 
		}
		
		$filter_order     = $mainframe->getUserStateFromRequest( $context.$view.'filter_order_author','filter_order','member_id' );
		$filter_order_Dir = $mainframe->getUserStateFromRequest( $context.$view.'filter_order_Dir',  'filter_order_Dir', 'DESC' );
		
		if($total == 0){
			$query .= ' ORDER BY '.$filter_order.' '.$filter_order_Dir;
			$this->_db->setQuery($query, $this->_limitstart, $this->_limit );
			return $this->_db->loadObjectList();
		}else{
			$this->_db->setQuery($query) ;
			return $this->_db->loadObject();
		}
	}
	
	public function getPaging($total = 0){
		jimport('joomla.html.pagination');
		$this->paging = new JPagination( $total , $this->_limitstart, $this->_limit );
		return $this->paging ;
	}
	
	function getListRedemtion($condition = '', $total = 0){
		$mainframe = JFactory::getApplication() ;
		$context = JRequest::getCmd('option');
		$view = JRequest::getCmd('view');
		
		if($total == 0){
			$query = '
			SELECT r.*, pro.name as pro_name 
			FROM #__wrd_redemption r 
			LEFT JOIN #__wrd_products pro ON pro.id = r.product_id
			LEFT JOIN #__wrd_profile p ON p.user_id = r.user_id 
			WHERE (1) ' ;
		}else{
			$query = 'SELECT count(r.id) as total 
			FROM #__wrd_redemption r 
			WHERE (1) ';
		}
		if(!empty($condition)){
			$query .= $condition ;
		}
		$search = $mainframe->getUserStateFromRequest( $context.$view.'search','search','','string');
   		$search = JString::strtolower( $search );
		if ($search){
			$query .= ' and ( p.member_id LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).'
					|| p.f_name LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).'	
					|| p.phone LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false )
					.')'; 
		}
		//filter by product
		$filter_product_id 	= $mainframe->getUserStateFromRequest( $context.$view.'product_id','product_id',-1,'int' );
		if ( $filter_product_id != -1 ){
			$query .= ' and r.product_id = '.$filter_product_id ;
		}
		
		//filter by outlet
		$filter_r_outlet 	= $mainframe->getUserStateFromRequest( $context.$view.'pick_up_at','pick_up_at',-1,'int' );
		if ( ($filter_r_outlet != -1) && !empty($filter_r_outlet) ){
			$query .= ' and p.pick_up_at = '.$filter_r_outlet ;
		}
		
		//filter by status of redemption
		$filter_r_status 	= $mainframe->getUserStateFromRequest( $context.$view.'status','status',-1,'int' );
		if (( $filter_r_status != -1 ) && !empty($filter_r_status) ){
			$query .= ' and r.status_of_redemption = '.$filter_r_status ;
		}
		
		//Filter by date_type && date
		$filter_type 	= $mainframe->getUserStateFromRequest( $context.$view.'filter_type','filter_type',-1,'int' );
		$filter_date_from 	= $mainframe->getUserStateFromRequest( $context.$view.'filter_date_from','filter_date_from',-1,'date' );
		$filter_date_to 	= $mainframe->getUserStateFromRequest( $context.$view.'filter_date_to','filter_date_to',-1,'date' );
		if (( $filter_type != -1 ) && !empty($filter_type) ){
			switch ($filter_type){
				case '1':
					$filter_type = ' r.exchange_date ' ;
					break ;
				case '2' :
					//Pick Up Date
					$filter_type = ' r.pick_up_date ' ;
					break;
				case '3' :
					//Deliver To Outlet Date
					$filter_type = '' ;
					$query .= ' and r.status_of_redemption = 4 ' ;
					$filter_type = ' r.status_of_redemption_date ' ;
					break;
			}
			if(!empty($filter_date_from)){
				$query .= ' and '.$filter_type.' >= "'.$filter_date_from.'"' ;
			}
			if(!empty($filter_date_to)){
				$query .= ' and '.$filter_type.' <= "'.$filter_date_to.'"' ;
			}
			
		}
		$filter_order     = $mainframe->getUserStateFromRequest( $context.$view.'filter_order_author','filter_order','id' );
		$filter_order_Dir = $mainframe->getUserStateFromRequest( $context.$view.'filter_order_Dir',  'filter_order_Dir', 'DESC' );
			
		if($total == 0){
			//order
			$query 	.= ' ORDER BY '.$filter_order.' '.$filter_order_Dir ;
			$this->_db->setQuery($query, $this->_limitstart, $this->_limit );
			return $this->_db->loadObjectList();
		}else{
			$this->_db->setQuery($query);
			return $this->_db->loadObject();
		}
	}
	
	public function send_email($cid = array(), $type = 'redemp_notice_email')
	{
		$db = JFactory::getDbo();
		if (count( $cid )){
			
			if($type == 'redemp_notice_email'){
				$type = 'redemption_notice' ;
			}else if($type == 'redemp_pick_up_notice'){
				$type = 'redemption_pick_up_notice' ;
			}
			JArrayHelper::toInteger($cid);
			foreach($cid as $k=>$v){
				$notice_details = $this->getNoticeDetails($v);
				$user_profile[$notice_details->user_id] = $this->getUserProfile($notice_details->user_id);
			}
			foreach($user_profile as $p){
				$sql = ' select name from #__wrd_lprefer where `id` = "'.$p->lprefer.'"' ;
				$db->setQuery($sql) ;
				$rs = $db->loadObject();
				if(!empty($p->p_email)){
					//get email template and send to user
					$this->send_email_template($type,$p, $rs->name);	
				}else{ 
					$user_email = $this->getUserEmail($p->user_id) ;
					$p->p_email = $user_email ;
					if(!empty($p->p_email)){
						$this->send_email_template($type,$p, $rs->name);
					}	
				}
			}
		}
		return true;
	}
	
	public function send_email_template($type='redemption_notice',$user_profile,$lang='English'){
		if(!empty($user_profile)){
			$db = JFactory::getDbo();
			$sql = ' select * from #__wrd_cemail where `action` = "'.$type.'"' ;
			$db->setQuery($sql) ;
			$rs = $db->loadObject();
			$config = JFactory::getConfig();
			$data['fromname']	= $config->get('fromname');
  			$data['mailfrom']	= $config->get('mailfrom');
  			$data['sitename']	= $config->get('sitename');
			if(!empty($rs)){
				if($lang == 'Vietnamese'){
					$emailBody = $rs->template_vi ;
				}else{
					$emailBody = $rs->template ;
				}
				$cc = split(',', $rs->email) ;
				$emailSubject = '' ;
				$emailBody = str_replace('{line-break}', '<br />', $emailBody);
				$emailBody = str_replace('{f_name}', $user_profile->f_name, $emailBody);
				$emailBody = str_replace('{l_name}', $user_profile->l_name, $emailBody);
				$emailBody = str_replace('{member_id}', $user_profile->member_id, $emailBody);
				$p_o_u = WrdHelper::getPoints($user_profile->user_id) ;
				if($type=='redemption_notice'){
					$emailSubject = 'Redemption notice';
					$redemp_notice = $this->getRedemptionNotice($user_profile->user_id) ;
					$point_taken_redemp_notice = $this->getPointTakenRedemptionNotice($user_profile->user_id) ;
					$form .= "<table cellpadding='5' cellspacing='0' style='border: 1px solid #d0d0d0;'>
								<tr>
									<td style='border: 1px solid #d0d0d0;'>Last Name</td>
									<td style='border: 1px solid #d0d0d0;'>First Name</td>
									<td style='border: 1px solid #d0d0d0;'>VIPCard number</td>
									<td style='border: 1px solid #d0d0d0;'>Gift name</td>
									<td style='border: 1px solid #d0d0d0;'>Quantity</td>
									<td style='border: 1px solid #d0d0d0;'>Redemption code</td>
									<td style='border: 1px solid #d0d0d0;'>Points Taken</td>
									<td style='border: 1px solid #d0d0d0;'>Points balance</td>
								</tr>" ;
					$form .= "<tr>
								<td style='border: 1px solid #d0d0d0;' rowspan=".count($redemp_notice).">".$user_profile->l_name."</td>
								<td style='border: 1px solid #d0d0d0;' rowspan=".count($redemp_notice).">".$user_profile->f_name."</td>
								<td style='border: 1px solid #d0d0d0;' rowspan=".count($redemp_notice).">".$user_profile->member_id."</td>";
								if(count($redemp_notice) == 0){
									$form .= "<td style='border: 1px solid #d0d0d0;'>&nbsp;</td>
											  <td style='border: 1px solid #d0d0d0;'>&nbsp;</td>
											  <td style='border: 1px solid #d0d0d0;'>&nbsp;</td>" ;
									$form .= "<td style='border: 1px solid #d0d0d0;' rowspan=".count($redemp_notice).">&nbsp;</td>
											  <td style='border: 1px solid #d0d0d0;' rowspan=".count($redemp_notice).">&nbsp;</td>";
								}else{
									for($ti = 0; $ti < count($redemp_notice);$ti++){
										$product_name = $this->getProductName($redemp_notice[$ti]->product_id) ;
										if($ti == 0){			
											$form .= "<td style='border: 1px solid #d0d0d0;'>&nbsp;".$product_name."</td>
													  <td style='border: 1px solid #d0d0d0;'>&nbsp;".$redemp_notice[$ti]->quantity."</td>
													  <td style='border: 1px solid #d0d0d0;'>&nbsp;".$redemp_notice[$ti]->code."</td>" ;
											$form .= "<td style='border: 1px solid #d0d0d0;' rowspan=".count($redemp_notice).">".$point_taken_redemp_notice."</td>
													  <td style='border: 1px solid #d0d0d0;' rowspan=".count($redemp_notice).">".$p_o_u."</td>";
											
										}else{
											$form .="<tr><td style='border: 1px solid #d0d0d0;'>".$product_name."</td>
											<td style='border: 1px solid #d0d0d0;'>".$redemp_notice[$ti]->quantity."</td>
											<td style='border: 1px solid #d0d0d0;'>".$redemp_notice[$ti]->code."</td>" ;
										}
									}
								}
					$form .= "</tr></table>" ;
					$form = sprintf($form) ; 
					$emailBody = str_replace('{form}', $form, $emailBody);
				}else if($type=='redemption_pick_up_notice'){
					$emailSubject = 'Redemption pick up notice' ;
					$redemp_notice = $this->getRedemptionPickupNotice($user_profile->user_id) ;
					$form = "<table cellpadding='5' cellspacing='0' style='border: 1px solid #d0d0d0;'>
								<tr>
									<td style='border: 1px solid #d0d0d0;'>Last Name</td>
									<td style='border: 1px solid #d0d0d0;'>First Name</td>
									<td style='border: 1px solid #d0d0d0;'>Gift name</td>
									<td style='border: 1px solid #d0d0d0;'>Quantity</td>
									<td style='border: 1px solid #d0d0d0;'>Pick up outlet</td>
								</tr>" ;
					$form .= "<tr>
								<td style='border: 1px solid #d0d0d0;' rowspan=".count($redemp_notice).">".$user_profile->l_name."</td>
								<td style='border: 1px solid #d0d0d0;' rowspan=".count($redemp_notice).">".$user_profile->f_name."</td>";
								if(count($redemp_notice)==0){
									$form .= "<td style='border: 1px solid #d0d0d0;'>&nbsp;</td>
											  <td style='border: 1px solid #d0d0d0;'>&nbsp;</td>" ;
									$form .= "<td style='border: 1px solid #d0d0d0;' rowspan=".count($redemp_notice).">&nbsp;</td>";
								}else{
									for($ti = 0; $ti < count($redemp_notice);$ti++){
										$product_name = $this->getProductName($redemp_notice[$ti]->product_id) ;
										$outlet_name = $this->getOutletName($redemp_notice[$ti]->pick_up_at) ;
										if($ti == 0){			
											$form .= "<td style='border: 1px solid #d0d0d0;'>&nbsp;".$product_name."</td>
													  <td style='border: 1px solid #d0d0d0;'>&nbsp;".$redemp_notice[$ti]->quantity."</td>" ;
											$form .= "<td style='border: 1px solid #d0d0d0;'>&nbsp;".$outlet_name."</td>";
											
										}else{
											$form .="<tr><td style='border: 1px solid #d0d0d0;'>&nbsp;".$product_name."</td>
													<td style='border: 1px solid #d0d0d0;'>&nbsp;".$redemp_notice[$ti]->quantity."</td>" ;
											$form .= "<td style='border: 1px solid #d0d0d0;'>&nbsp;".$outlet_name."</td>";
										}
									}
								}
					$form .= "</tr></table>" ; 
					$emailBody = str_replace('{form}', $form , $emailBody);
				}
				$return = JUtility::sendMail($data['mailfrom'], $data['fromname'], $user_profile->p_email, $emailSubject, $emailBody,1,$cc);
			}
			return true ;
		}
	}

	public function getRedemptionNotice($uid = 0){
		$db = JFactory::getDbo();	
		$query 	= 'SELECT * FROM #__wrd_redemption WHERE user_id = "'.$uid.'" and approved = 1 and (status_of_redemption=2 || status_of_redemption=3 || status_of_redemption=4)';
		$db->setQuery($query);
		return $db->loadObjectList();
	}
	
	public function getPointTakenRedemptionNotice($uid = 0){
		$db = JFactory::getDbo();	
		$query 	= 'SELECT sum(point) as total_point_taken FROM #__wrd_redemption WHERE user_id = "'.$uid.'" and approved = 1 and (status_of_redemption=2 || status_of_redemption=3 || status_of_redemption=4)';
		$db->setQuery($query);
		$rs = $db->loadObject();
		return $rs->total_point_taken ;
	}
	
	public function getRedemptionPickupNotice($uid = 0){
		$db = JFactory::getDbo();	
		$query 	= 'SELECT * FROM #__wrd_redemption WHERE user_id = "'.$uid.'" and approved = 1 and status_of_redemption=4';
		$db->setQuery($query);
		return $db->loadObjectList();
	}
	
	public function getProductName($pid = 0){
		$db = JFactory::getDbo();	
		$query 	= 'SELECT name FROM #__wrd_products WHERE id = "'.$pid.'"';
		$db->setQuery($query);
		$rs = $db->loadObject();
		return $rs->name ;
	}
	
	public function getOutletName($id = 0){
		$db = JFactory::getDbo();	
		$query 	= 'SELECT name FROM #__wrd_outlets WHERE id = "'.$id.'"';
		$db->setQuery($query);
		$rs = $db->loadObject();
		return $rs->name ;
	}
	
	public function getUserProfile($id = 0){
  		$db = JFactory::getDbo();	
		$query = 'SELECT *
  			FROM #__wrd_profile
  			WHERE user_id = '.$id;
  		$db->setQuery($query);
		return $db->loadObject();
  	}
  	
  	public function getNoticeDetails($id = 0){
  		$db = JFactory::getDbo();	
		$query = '
			SELECT *
  			FROM #__wrd_redemption
  			WHERE id = '.$id ;
  		$db->setQuery($query);
		return $db->loadObject();
  	}

	public function getUserEmail($id=0){
		$query = 'SELECT a.email,b.p_email,alternative_email
		FROM #__users as a 
		LEFT JOIN #__wrd_profile as b on a.id = b.user_id
		WHERE a.id =  '.$id;
		
		$this->_db->setQuery($query);
		$rs = $this->_db->loadObject();
		if($rs->p_email != '') return $rs->p_email ;
		elseif($rs->alternative_email != '') return $rs->alternative_email ;
		else return $rs->email ;
  	} 	
}